package com.cn.ccp.service.course;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.cn.ccp.bean.HomeworkReportBean;
import com.cn.ccp.bean.OnlineExamBean;
import com.cn.ccp.bean.RecordInvestigationBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;

/**
 * 课程调查service
 * @author renyongcheng
 *
 */
public class CourseInvestigationService extends BasicService{
	private Log log = LogFactory.getLog(CourseInvestigationService.class);
	
	/**
	 * 学生提交调查
	 * @param list
	 */
	public void commintInvestigation(final String settingId,final List<OnlineExamBean> list){
		 transactionTemplate.execute(new TransactionCallback() {
			@Override
			public Object doInTransaction(TransactionStatus ts) {
				try{
					 String sql = "delete from record_investigation where investigation_id = ? and user_id=?";
					 jdbcTemplate.update(sql , new Object[]{ settingId,CCPContants.getCurrUserId()});	
					 sql = "delete from course_setting_record where setting_id = ? and user_id=?";
					 jdbcTemplate.update(sql , new Object[]{ settingId,CCPContants.getCurrUserId()});	
					 
					 
					 sql = "insert into record_investigation( investigation_id,training_id,choose,user_id,commit_time) VALUES (?,?,?,?,CURRENT_TIMESTAMP) ";	
					 for(OnlineExamBean b : list){
						 jdbcTemplate.update(sql , new Object[]{ settingId,b.getIndex(),b.getSelectAnswer(),CCPContants.getCurrUserId()});		
					 }
					 sql = "insert into course_setting_record( setting_id,user_id,other) VALUES (?,?,CURRENT_TIMESTAMP) ";	
					 jdbcTemplate.update(sql , new Object[]{ settingId,CCPContants.getCurrUserId()});		
					return Boolean.TRUE;
				}catch(Exception e){
					log.error("commint research failed  : ",e);
					ts.setRollbackOnly();  
					return Boolean.FALSE; 
				}
			}
		});
	}
	
	/**
	 * 学生提交调查
	 * @param list
	 */
	public void commintInvestigation(final String settingId,final List<OnlineExamBean> list,final String userId){
		 transactionTemplate.execute(new TransactionCallback() {
			@Override
			public Object doInTransaction(TransactionStatus ts) {
				try{
					 String sql = "delete from record_investigation where investigation_id = ? and user_id=?";
					 jdbcTemplate.update(sql , new Object[]{ settingId,userId});	
					 sql = "delete from course_setting_record where setting_id = ? and user_id=?";
					 jdbcTemplate.update(sql , new Object[]{ settingId,userId});	
					 
					 
					 sql = "insert into record_investigation( investigation_id,training_id,choose,user_id,commit_time) VALUES (?,?,?,?,CURRENT_TIMESTAMP) ";	
					 for(OnlineExamBean b : list){
						 jdbcTemplate.update(sql , new Object[]{ settingId,b.getIndex(),b.getSelectAnswer(),userId});		
					 }
					 sql = "insert into course_setting_record( setting_id,user_id,other) VALUES (?,?,CURRENT_TIMESTAMP) ";	
					 jdbcTemplate.update(sql , new Object[]{ settingId,userId});		
					return Boolean.TRUE;
				}catch(Exception e){
					log.error("commint research failed  : ",e);
					ts.setRollbackOnly();  
					return Boolean.FALSE; 
				}
			}
		});
	}
	
	
	/**
	 * 学生提交考试成绩
	 * @param settingId：试题编号
	 * @param score：分数
	 * @param list：试题答案
	 */
	public void commintCourseSetting(final String settingId,final String selectCourseId,final String score,final String status,final List<OnlineExamBean> list){
		 transactionTemplate.execute(new TransactionCallback() {
			@Override
			public Object doInTransaction(TransactionStatus ts) {
				try{
					 String sql = "delete from record_investigation where investigation_id = ? and user_id=?";
					 jdbcTemplate.update(sql , new Object[]{ settingId,CCPContants.getCurrUserId()});	
					 sql = "delete from course_setting_record where setting_id = ? and user_id=?";
					 jdbcTemplate.update(sql , new Object[]{ settingId,CCPContants.getCurrUserId()});	
					 
					sql = "UPDATE selected_course SET score = ? , status = ? WHERE id = ?";
					jdbcTemplate.update(sql,new Object[]{score, status ,selectCourseId});
						
					sql = "DELETE FROM student_training_record WHERE selected_course_id = ?";
					jdbcTemplate.update(sql ,new Object[]{selectCourseId});
					 
					 
					 sql = "insert into record_investigation( investigation_id,training_id,choose,user_id,commit_time) VALUES (?,?,?,?,CURRENT_TIMESTAMP) ";	
					 for(OnlineExamBean b : list){
						 jdbcTemplate.update(sql , new Object[]{ settingId,b.getIndex(),b.getSelectAnswer(),CCPContants.getCurrUserId()});		
					 }
					 sql = "insert into course_setting_record( setting_id,user_id,score,other) VALUES (?,?,?,CURRENT_TIMESTAMP) ";	
					 jdbcTemplate.update(sql , new Object[]{ settingId,CCPContants.getCurrUserId(),score});		
					return Boolean.TRUE;
				}catch(Exception e){
					log.error("commint research failed  : ",e);
					ts.setRollbackOnly();  
					return Boolean.FALSE; 
				}
			}
		});
	} 
	
	/**
	 * 学生提交考试成绩
	 * @param settingId：试题编号
	 * @param score：分数
	 * @param list：试题答案
	 */
	public void commintCourseSetting(final String userId,final String settingId,final String selectCourseId,final String score,final String status,final List<OnlineExamBean> list){
		 transactionTemplate.execute(new TransactionCallback() {
			@Override
			public Object doInTransaction(TransactionStatus ts) {
				try{
					 String sql = "delete from record_investigation where investigation_id = ? and user_id=?";
					 jdbcTemplate.update(sql , new Object[]{ settingId,userId});	
					 sql = "delete from course_setting_record where setting_id = ? and user_id=?";
					 jdbcTemplate.update(sql , new Object[]{ settingId,userId});	
					 
					sql = "UPDATE selected_course SET score = ? , status = ? WHERE id = ?";
					jdbcTemplate.update(sql,new Object[]{score, status ,selectCourseId});
						
					sql = "DELETE FROM student_training_record WHERE selected_course_id = ?";
					jdbcTemplate.update(sql ,new Object[]{selectCourseId});
					 
					 
					 sql = "insert into record_investigation( investigation_id,training_id,choose,user_id,commit_time) VALUES (?,?,?,?,CURRENT_TIMESTAMP) ";	
					 for(OnlineExamBean b : list){
						 jdbcTemplate.update(sql , new Object[]{ settingId,b.getIndex(),b.getSelectAnswer(),userId});		
					 }
					 sql = "insert into course_setting_record( setting_id,user_id,score,other) VALUES (?,?,?,CURRENT_TIMESTAMP) ";	
					 jdbcTemplate.update(sql , new Object[]{ settingId,userId,score});		
					return Boolean.TRUE;
				}catch(Exception e){
					log.error("commint research failed  : ",e);
					ts.setRollbackOnly();  
					return Boolean.FALSE; 
				}
			}
		});
	} 
	
	
	
	public void updateCourseSetRecord(String settingId ,String score){
		String sql = "insert into course_setting_record( setting_id,user_id,score) VALUES (?,?,?) ";	
		 jdbcTemplate.update(sql , new Object[]{ settingId,CCPContants.getCurrUserId(),score});
		 
	}
	
	public void updateCourseSetRecord(String settingId ){
		String sql = "insert into course_setting_record( setting_id,user_id) VALUES (?,?) ";	
		 jdbcTemplate.update(sql , new Object[]{ settingId,CCPContants.getCurrUserId()});
		 
	}
	
	/**
	 * 学生提交作业
	 * @param settingId
	 */
	public void commintTraining(final String settingId){
		 transactionTemplate.execute(new TransactionCallback() {
			@Override
			public Object doInTransaction(TransactionStatus ts) {
				try{
					 String sql   = "insert into course_setting_record( setting_id,user_id) VALUES (?,?) ";	
					 jdbcTemplate.update(sql , new Object[]{settingId,CCPContants.getCurrUserId()});		
					return Boolean.TRUE;
				}catch(Exception e){
					log.error("commint research failed  : ",e);
					ts.setRollbackOnly();  
					return Boolean.FALSE; 
				}
			}
		});
	}
	
	
	/**
	 * 根据调查id
	 * @param inverId
	 * @return
	 */
	public List<RecordInvestigationBean> getRecordInvestigation(String inverId){
		StringBuffer sql = new StringBuffer();
		sql.append(" select ri.investigation_id,ri.training_id,ri.choose,count(ri.user_id) as ccount ");
		sql.append(" from record_investigation ri ");
		sql.append(" where ri.investigation_id = ? ");
		sql.append(" group by ri.training_id,ri.choose ");
		log.info(sql);
		return jdbcTemplate.query(sql.toString() , new RecordInvestigationRowMapper());
	}
	
	
	public List<HomeworkReportBean> getHomeworkReportList(String courseId){
		StringBuffer sql = new StringBuffer();
		sql.append(" select cset.*,csetr.user_id,u.username from course_setting_record csetr ");
		sql.append(" left join course_setting cset on cset.id = csetr.setting_id ");
		sql.append(" left outer join user u on u.id = csetr.user_id ");
		sql.append(" where cset.course_id = ? and cset.type= ?");
		log.info(sql);
		return jdbcTemplate.query(sql.toString(),new Object[]{courseId,CCPContants.getCurrUserId()} , new RecordHomeworkRowMapper());
	}
	
	
	
	class RecordInvestigationRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			RecordInvestigationBean bean = new RecordInvestigationBean();
			bean.setId(rs.getString("id"));
			bean.setTrainingId(rs.getString("training_id"));
			bean.setChoose(rs.getString("choose"));
			bean.setCount(rs.getInt("ccount"));
			return bean;
		}
	}
	
	class RecordHomeworkRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			HomeworkReportBean bean = new HomeworkReportBean();
			bean.setCourseId(rs.getString("course_id")); 
			bean.setHomeworkId(rs.getString("id"));
			bean.setUserId(rs.getString("user_id"));
			bean.setUserName(rs.getString("username"));
			bean.setDoneTime(rs.getString("createtime"));
			return bean;
		}
	}
	
	
}
